IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spAttachmentSelectByCompanyCodeAndPeriod]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[spAttachmentSelectByCompanyCodeAndPeriod]
GO
-- =============================================
-- Author:		Nam Tran
-- Create date: Sep 21, 2014
-- Description:	select attachment of pressrelease that missing content
-- =============================================
CREATE PROCEDURE [dbo].[spAttachmentSelectByCompanyCodeAndPeriod]
	@SourceCode	VARCHAR(50),
	@LangId	INT = NULL,
	@FromDate	DATETIME = '2005-01-01',
	@ToDate		DATETIME = NULL
AS
BEGIN
	SET NOCOUNT ON;
	IF(@ToDate IS NULL)
		SET @ToDate = GETDATE();
		
	IF(@LangId IS NOT NULL)
		BEGIN
			SELECT	Id
					,PressreleaseId
					,FileName
					,Location 
					,DisplayOrder [Order]
					,LangId LanguageID
					,MimeType Mime
			FROM	Attachment
			WHERE	MimeType = 'application/pdf'
					AND PressreleaseId IN (
							SELECT	Id
							FROM	Pressreleases
							WHERE	(Message IS NULL OR Message = '')
									AND LangId = @LangId
									AND SourceCode = @SourceCode
									AND [DateTime] BETWEEN @FromDate AND @ToDate)
		END
	ELSE
		BEGIN
			SELECT	Id
					,PressreleaseId 
					,FileName
					,Location
					,DisplayOrder [Order]
					,LangId LanguageID
					,MimeType Mime
			FROM	Attachment
			WHERE	MimeType = 'application/pdf'
					AND PressreleaseId IN (
							SELECT	Id 
							FROM	Pressreleases
							WHERE	(Message IS NULL OR Message = '')
									AND SourceCode = @SourceCode
									AND [DateTime] BETWEEN @FromDate AND @ToDate)
		END
END
